package comptadub

import com.google.gdata.client.spreadsheet.SpreadsheetService
import com.google.gdata.data.spreadsheet.CellEntry
import com.google.gdata.data.spreadsheet.CellFeed
import com.google.gdata.data.spreadsheet.SpreadsheetFeed
import com.google.gdata.data.spreadsheet.Worksheet
import com.google.gdata.data.spreadsheet.WorksheetEntry

class SpreadSheetService {
	
	def grailsApplication

	def getSolde(Long idMembre) {
		def membre = Membre.get(idMembre)
		def codes = []
		def valeurs = []
		connectSpreadSheet(membre.code, codes, valeurs)
		codes.add('erreur') // pour afficher qqch en cas d'absence de code
		valeurs.add(9999.99)

		def index = codes.indexOf(membre.code.minus(" "))
		membre.solde = valeurs[index]
		membre.save()
		
	}
	
	def connectSpreadSheet(String code, List codes, List valeurs) {
		def username = grailsApplication.config.grails.mail.username // "comptadub@gmail.com"
		def password = grailsApplication.config.grails.mail.password // "dampeel21"

		SpreadsheetService service = new SpreadsheetService("MySpreadsheetIntegration-v1");
		service.setUserCredentials(username, password);
		URL sheetFeedUrl = new URL("https://spreadsheets.google.com/feeds/spreadsheets/private/full")
		SpreadsheetFeed feed = service.getFeed(sheetFeedUrl,SpreadsheetFeed.class);
		def spreadsheets = feed.entries
		List worksheets = spreadsheets.first.worksheets // premier fichier
		WorksheetEntry ws = worksheets.last // dernier onglet

		URL cellFeedUrlCodes = new URI(ws.getCellFeedUrl().toString() + "?min-row=4&max-row=4&min-col=31&max-col=59").toURL();
		CellFeed cellFeedCodes = service.getFeed(cellFeedUrlCodes, CellFeed.class);

		cellFeedCodes.entries.each { cellEntry->
			codes.add(cellEntry.cell.value.minus(" "))
		}

		URL cellFeedUrlValeurs = new URI(ws.getCellFeedUrl().toString() + "?min-row=5&max-row=5&min-col=31&max-col=59").toURL();
		CellFeed cellFeedValeurs = service.getFeed(cellFeedUrlValeurs, CellFeed.class);

		cellFeedValeurs.entries.each { cellEntry->
			valeurs.add(cellEntry.cell.value.minus("€").minus(" ").replaceFirst(",",".").toFloat()*-1)
		}
	}
}
